#![allow(unused_variables)]
use sqlx::Error;
use super::CountNum;
use super::GoodsBean;
use super::GoodsQueryRequest;
use super::Table;

impl<'c> Table<'c, GoodsBean> {
pub async fn activate_goods(&self,id:i64) ->Result<u64, Error> {
	let result = sqlx::query(
	  r#"update cs_goodses set active = true where id = ? "#
	)
	.bind(id)
	.execute(&(&*self.pool).clone().unwrap())
	.await;
	match result {
		Ok(result) => {
			Ok(result.rows_affected())
		},
		Err(err) => {
			Err(err)
		}
	}
}

pub async fn add_goods(&self,goods:GoodsBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"insert into cs_goodses ( name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new) values (?,?,from_base64(?),from_base64(?),from_base64(?),?,?,?,?,?,?,?)"#
		)
		.bind(goods.name)
		.bind(goods.active)
		.bind(goods.cover)
		.bind(goods.image1)
		.bind(goods.image2)
		.bind(goods.price)
		.bind(goods.intro)
		.bind(goods.stock)
		.bind(goods.goods_type_id)
		.bind(goods.is_scroll)
		.bind(goods.is_hot)
		.bind(goods.is_new)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn count_active_goods_records(&self) ->Result<CountNum, Error> {
		let result = sqlx::query_as(
		r#"select count(*) as countNum from cs_goodses where active = true"#
		)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn count_search_goodses_by_fields_records(&self,goods_query_request:GoodsQueryRequest) ->Result<CountNum, Error> {
		let mut query = r#"select count(*) as countNum from cs_goodses where 1=1 "#.to_string();
		let name = goods_query_request.name.unwrap_or_default();
		if !name.is_empty() {
			let query_name  = format!("{}{}{}"," and name like '%",name,"%' ");
			query = format!("{} {}", query, query_name);
		}
		let active = goods_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let price = goods_query_request.price.unwrap_or_default();
		if !price.is_empty() {
			let query_price  = format!("{}{}{}"," and price = ",price," ");
			query = format!("{} {}", query, query_price);
		}
		let intro = goods_query_request.intro.unwrap_or_default();
		if !intro.is_empty() {
			let query_intro  = format!("{}{}{}"," and intro like '%",intro,"%' ");
			query = format!("{} {}", query, query_intro);
		}
		let stock = goods_query_request.stock.unwrap_or_default();
		if !stock.is_empty() {
			let query_stock  = format!("{}{}{}"," and stock = ",stock," ");
			query = format!("{} {}", query, query_stock);
		}
		let goods_type_id = goods_query_request.goods_type_id.unwrap_or_default();
		if !goods_type_id.is_empty() {
			let query_goods_type_id  = format!("{}{}{}"," and goods_type_id = ",goods_type_id," ");
			query = format!("{} {}", query, query_goods_type_id);
		}
		let is_scroll = goods_query_request.is_scroll.unwrap_or_default();
		if !is_scroll.is_empty() && (is_scroll=="true" || is_scroll =="false"){
			let query_is_scroll  = format!("{}{}{}"," and is_scroll = ",is_scroll," ");
			query = format!("{} {}", query, query_is_scroll);
		}
		let is_hot = goods_query_request.is_hot.unwrap_or_default();
		if !is_hot.is_empty() && (is_hot=="true" || is_hot =="false"){
			let query_is_hot  = format!("{}{}{}"," and is_hot = ",is_hot," ");
			query = format!("{} {}", query, query_is_hot);
		}
		let is_new = goods_query_request.is_new.unwrap_or_default();
		if !is_new.is_empty() && (is_new=="true" || is_new =="false"){
			let query_is_new  = format!("{}{}{}"," and is_new = ",is_new," ");
			query = format!("{} {}", query, query_is_new);
		}
		let result = sqlx::query_as(&query)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn delete_goods(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"delete from cs_goodses where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_goods_by_id(&self,id:i64) ->Result<GoodsBean, Error> {
	let result = sqlx::query_as(
	r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where id = ? "#
		)
		.bind(id)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn find_goods_by_name(&self,name:String) ->Result<GoodsBean, Error> {
	let result = sqlx::query_as(
	r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where name = ?;"#
		)
		.bind(name)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_active_goodses(&self) ->Result<Vec<GoodsBean>, Error> {
		let result = sqlx::query_as(
		  r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where active = true order by name asc"#
		)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_all_goodses(&self) ->Result<Vec<GoodsBean>, Error> {
		let result = sqlx::query_as(
	r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses"#
		)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn search_goodses_by_fields(&self,goods_query_request:GoodsQueryRequest) ->Result<Vec<GoodsBean>, Error> {
		let mut query = r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where 1=1 "#.to_string();
		let name = goods_query_request.name.unwrap_or_default();
		if !name.is_empty() {
			let query_name  = format!("{}{}{}"," and name like '%",name,"%' ");
			query = format!("{} {}", query, query_name);
		}
		let active = goods_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let price = goods_query_request.price.unwrap_or_default();
		if !price.is_empty() {
			let query_price  = format!("{}{}{}"," and price = ",price," ");
			query = format!("{} {}", query, query_price);
		}
		let intro = goods_query_request.intro.unwrap_or_default();
		if !intro.is_empty() {
			let query_intro  = format!("{}{}{}"," and intro like '%",intro,"%' ");
			query = format!("{} {}", query, query_intro);
		}
		let stock = goods_query_request.stock.unwrap_or_default();
		if !stock.is_empty() {
			let query_stock  = format!("{}{}{}"," and stock = ",stock," ");
			query = format!("{} {}", query, query_stock);
		}
		let goods_type_id = goods_query_request.goods_type_id.unwrap_or_default();
		if !goods_type_id.is_empty() {
			let query_goods_type_id  = format!("{}{}{}"," and goods_type_id = ",goods_type_id," ");
			query = format!("{} {}", query, query_goods_type_id);
		}
		let is_scroll = goods_query_request.is_scroll.unwrap_or_default();
		if !is_scroll.is_empty() && (is_scroll=="true" || is_scroll =="false"){
			let query_is_scroll  = format!("{}{}{}"," and is_scroll = ",is_scroll," ");
			query = format!("{} {}", query, query_is_scroll);
		}
		let is_hot = goods_query_request.is_hot.unwrap_or_default();
		if !is_hot.is_empty() && (is_hot=="true" || is_hot =="false"){
			let query_is_hot  = format!("{}{}{}"," and is_hot = ",is_hot," ");
			query = format!("{} {}", query, query_is_hot);
		}
		let is_new = goods_query_request.is_new.unwrap_or_default();
		if !is_new.is_empty() && (is_new=="true" || is_new =="false"){
			let query_is_new  = format!("{}{}{}"," and is_new = ",is_new," ");
			query = format!("{} {}", query, query_is_new);
		}
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn search_goodses_by_fields_by_page(&self,goods_query_request:GoodsQueryRequest) ->Result<Vec<GoodsBean>, Error> {
		let mut query = r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where 1=1 "#.to_string();
		let name = goods_query_request.name.unwrap_or_default();
		if !name.is_empty() {
			let query_name  = format!("{}{}{}"," and name like '%",name,"%' ");
			query = format!("{} {}", query, query_name);
		}
		let active = goods_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let price = goods_query_request.price.unwrap_or_default();
		if !price.is_empty() {
			let query_price  = format!("{}{}{}"," and price = ",price," ");
			query = format!("{} {}", query, query_price);
		}
		let intro = goods_query_request.intro.unwrap_or_default();
		if !intro.is_empty() {
			let query_intro  = format!("{}{}{}"," and intro like '%",intro,"%' ");
			query = format!("{} {}", query, query_intro);
		}
		let stock = goods_query_request.stock.unwrap_or_default();
		if !stock.is_empty() {
			let query_stock  = format!("{}{}{}"," and stock = ",stock," ");
			query = format!("{} {}", query, query_stock);
		}
		let goods_type_id = goods_query_request.goods_type_id.unwrap_or_default();
		if !goods_type_id.is_empty() {
			let query_goods_type_id  = format!("{}{}{}"," and goods_type_id = ",goods_type_id," ");
			query = format!("{} {}", query, query_goods_type_id);
		}
		let is_scroll = goods_query_request.is_scroll.unwrap_or_default();
		if !is_scroll.is_empty() && (is_scroll=="true" || is_scroll =="false"){
			let query_is_scroll  = format!("{}{}{}"," and is_scroll = ",is_scroll," ");
			query = format!("{} {}", query, query_is_scroll);
		}
		let is_hot = goods_query_request.is_hot.unwrap_or_default();
		if !is_hot.is_empty() && (is_hot=="true" || is_hot =="false"){
			let query_is_hot  = format!("{}{}{}"," and is_hot = ",is_hot," ");
			query = format!("{} {}", query, query_is_hot);
		}
		let is_new = goods_query_request.is_new.unwrap_or_default();
		if !is_new.is_empty() && (is_new=="true" || is_new =="false"){
			let query_is_new  = format!("{}{}{}"," and is_new = ",is_new," ");
			query = format!("{} {}", query, query_is_new);
		}
		let pagenum =  goods_query_request.page.unwrap_or_default();
		let pagesize =  goods_query_request.rows.unwrap_or_default();
		let start = (pagenum - 1) * pagesize;
		let limit = pagesize;
		query = format!("{} limit {} offset {} ", query, limit, start);
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn soft_delete_goods(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_goodses set active = false where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn toggle_goods(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
		  r#"update cs_goodses set active = not active where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn update_goods(&self,goods:GoodsBean) ->Result<u64, Error> {
		let result = sqlx::query(
		  r#"update cs_goodses set name = ? ,active = ? ,cover = from_base64(?) ,image1 = from_base64(?) ,image2 = from_base64(?) ,price = ? ,intro = ? ,stock = ? ,goods_type_id = ? ,is_scroll = ? ,is_hot = ? ,is_new = ? where id = ? "#
		)
		.bind(goods.name)
		.bind(goods.active)
		.bind(goods.cover)
		.bind(goods.image1)
		.bind(goods.image2)
		.bind(goods.price)
		.bind(goods.intro)
		.bind(goods.stock)
		.bind(goods.goods_type_id)
		.bind(goods.is_scroll)
		.bind(goods.is_hot)
		.bind(goods.is_new)
		.bind(goods.id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_goods_indexed_name(&self,short_name:String) ->Result<Vec<GoodsBean>, Error> {
	let result = sqlx::query_as(
	r#"select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where name like concat(? ,'%') "#
	)
	.bind(short_name)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await;
	return result;
}

pub async fn get_recommend_count_of_goods_by_type_id(&self,goods_type_id:i64)->Result<CountNum, Error> {
    if goods_type_id==0 {
        return self.get_count_of_goods_by_type_id(0).await;
    }
   	let result = sqlx::query_as(
	r#"select count(*) as countNum from cs_recommends where goods_type_id=? and active = true"#
	)
	.bind(goods_type_id)
	.fetch_one(&(&*self.pool).clone().unwrap())
	.await;
	return result;
}

pub async fn get_count_of_goods_by_type_id(&self,goods_type_id:i64)->Result<CountNum, Error> {
    if goods_type_id==0 {
       	let result = sqlx::query_as(
		r#"select count(*) as countNum from cs_goodses where active = true and cover is not null"#
		)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
    }
    else
    {
       	let result = sqlx::query_as(
		r#"select count(*) as countNum from cs_goodses where goods_type_id=? and active = true and cover is not null"#
		)
		.bind(goods_type_id)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
    }
}

pub async fn select_goods_by_recommend(&self,recommend_type_id:i64,page_number:i32,page_size:i32) -> Result<Vec<GoodsBean>, Error>{
    let start = (page_number - 1) * page_size;
	let limit = page_size;
    if recommend_type_id==0 {
        let query = " select g.id,g.name,g.active,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,g.goods_type_id,g.is_scroll,g.is_hot,g.is_new from cs_goodses g,cs_goods_types t where g.goods_type_id=t.id and g.active = true and g.cover is not null order by g.id limit ? offset ?";
        let result = sqlx::query_as(&query)
        .bind(limit)
        .bind(start)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
        return result;
    } else {
        let query = " select  g.id,g.name,g.active,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,g.goods_type_id,g.is_scroll,g.is_hot,g.is_new from cs_goodses g,cs_recommends r where g.id=r.goods_id and r.goods_type_id=? and g.active = true and g.cover is not null order by g.id limit ? offset ?";
        let result = sqlx::query_as(&query)
            .bind(recommend_type_id)
            .bind(limit)
            .bind(start)
    		.fetch_all(&(&*self.pool).clone().unwrap())
    		.await;
        return result;
    }
}

pub async fn get_scroll_goodses(&self,) -> Result<Vec<GoodsBean>, Error> {
    sqlx::query_as(
	  r#"select g.id,g.name,g.active,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,g.goods_type_id,g.is_scroll,g.is_hot,g.is_new from cs_recommends r, cs_goodses g where  r.goods_type_id=1 and r.goods_id=g.id and g.active = true and g.cover is not null order by g.id asc"#
	)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await
}

pub async fn get_goods_list(&self,recommendType:i64) -> Result<Vec<GoodsBean>, Error> {
    sqlx::query_as(
	  r#"select g.id,g.name,g.active,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,g.goods_type_id,g.is_scroll,g.is_hot,g.is_new from cs_recommends r, cs_goodses g where r.goods_id=g.id and r.goods_type_id = ? and g.active = true and g.cover is not null order by g.id asc"#
	)
	.bind(recommendType)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await
}

pub async fn select_goods_by_goods_type_id(&self,goods_type_id:i64,page_number:i32,page_size:i32) -> Result<Vec<GoodsBean>, Error> {
    let limit = page_size;
    let start = (page_number -1) * page_size;
    if goods_type_id ==0 {
        let result =  sqlx::query_as(
    	  r#"select g.id,g.name,g.active,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,g.goods_type_id,g.is_scroll,g.is_hot,g.is_new from cs_goodses g where g.active = true and g.cover is not null order by g.id asc limit ? offset ?"#
    	)
    	.bind(limit)
        .bind(start)
    	.fetch_all(&(&*self.pool).clone().unwrap())
    	.await;
        return result;
    } else {
        let result =  sqlx::query_as(
    	  r#"select g.id,g.name,g.active,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,g.goods_type_id,g.is_scroll,g.is_hot,g.is_new from cs_goodses g where g.goods_type_id = ? and g.active = true and g.cover is not null order by g.id asc limit ? offset ?"#
    	)
    	.bind(goods_type_id)
    	.bind(limit)
        .bind(start)
    	.fetch_all(&(&*self.pool).clone().unwrap())
    	.await;
        return result;
    }
}

pub async fn count_active_goodses_by_goods_type_id(&self,goods_type_id:i64) -> Result<CountNum, Error> {
    if goods_type_id ==0 {
        let result =  sqlx::query_as(
    	  r#"select count(g.id) as countNum from cs_goodses g where g.active = true and g.cover is not null"#
    	)
    	.fetch_one(&(&*self.pool).clone().unwrap())
    	.await;
        return result;
    } else {
        let result =  sqlx::query_as(
    	  r#"select count(g.id) as countNum from cs_goodses g where g.goods_type_id = ? and g.active = true and g.cover is not null"#
    	)
    	.bind(goods_type_id)
    	.fetch_one(&(&*self.pool).clone().unwrap())
    	.await;
        return result;
    }
}

pub async fn count_active_goodses_by_recommend_type_id(&self,recommend_type_id:i64) -> Result<CountNum, Error> {
    if recommend_type_id ==0 {
        let result =  sqlx::query_as(
    	  r#"select count(g.id) as countNum from cs_goodses g where g.active = true and g.cover is not null"#
    	)
    	.fetch_one(&(&*self.pool).clone().unwrap())
    	.await;
        return result;
    } else {
        let result =  sqlx::query_as(
    	  r#" select count(g.id) as countNum from cs_goodses g,cs_recommends r where g.id=r.goods_id and r.goods_type_id=? and g.active = true and g.cover is not null"#
    	)
    	.bind(recommend_type_id)
    	.fetch_one(&(&*self.pool).clone().unwrap())
    	.await;
        return result;
    }
}

pub async fn search_goods_by_page(&self,keyword:String,page_number:i32,page_size:i32) -> Result<Vec<GoodsBean>, Error> {
	let start = (page_number - 1) * page_size;
	let limit = page_size;
    let query = "select id,name,active,cover,image1,image2,price,intro,stock,goods_type_id,is_scroll,is_hot,is_new from cs_goodses where name like concat('%', ? ,'%') and active = true and cover is not null order by id asc limit ? offset ?";
    let result = sqlx::query_as(&query)
        .bind(keyword)
        .bind(limit)
        .bind(start)
  		.fetch_all(&(&*self.pool).clone().unwrap())
  		.await;
    return result;
}

}
